Snowflakeでステージにあるファイルのデータをクエリしてみた

こんにちは！エノカワです。

Snowflakeには、テーブルにロードするファイルの置き場所としてステージがあります。

LISTコマンドを使用することで、ステージに置かれたファイルのリストを取得することができますが、
実はSELECTステートメントを使用することでファイルの中身を取得することができます。

今回は、SELECTステートメントを使用してファイルのデータ取得を実際に試してみました。

使用するデータ

Citi Bikeというシェアサイクリングサービスの利用者に関するデータを使用します。
以下の記事を参考にロードするデータの準備を行います。

ロードするデータの準備が完了すると、以下が作成された状態になります。

• データベース：CITIBIKE
• テーブル：TRIPS
• ステージ：CITIBIKE_TRIPS
• ファイル形式：CSV

それでは、ステージCITIBIKE_TRIPSにあるファイルを確認してみましょう。

ファイルのリストを取得する

まずはファイルのリスト取得から行います。

ワークシート上で下記SQLを実行し、コンテキストを設定します。

// コンテキスト設定
USE ROLE SYSADMIN;
USE WAREHOUSE COMPUTE_WH;
USE CITIBIKE;
USE SCHEMA PUBLIC;

続いて、LISTコマンドを実行します。

// ステージングされたファイルを確認
list @CITIBIKE_TRIPS;

ステージングされたファイルについて、
ファイル名、サイズ、MD5ハッシュ、最終更新日の情報が取得できました。

次は、SELECTステートメントでファイルの中身を取得してみましょう。

ファイルのデータを取得する

Snowflakeは、ステージにあるデータファイルのクエリをサポートしています。

次のSQL文を実行して、ステージングされたデータファイルをクエリすることができます。

// 1番目と2番目のフィールドを指定
select $1, $2 from @CITIBIKE_TRIPS
  (file_format => CSV, pattern=>'citibike-trips/trips_2013_0_.*');

• $1, $2は、ファイルの列番号を表しており、1番目と2番目のフィールドを指定しています。
• フィールドを正しく解析するために、データ準備時に定義したCSVを指定しています。
• ファイル数が多いのでPATTERNオプションで対象ファイルを絞り込んでいます。

ステージングされたファイルについて、1番目と2番目のフィールドが取得できました。

ファイルパス指定

FROMにファイルパスを指定することで、ファイルを指定してクエリが可能です。

// ファイルパスを指定
select $1, $2 from @CITIBIKE_TRIPS/trips_2013_0_0_0.csv.gz
  (file_format => CSV);

存在しない列番号を指定した場合

ファイルに存在しない列番号を指定した場合はどうなるでしょうか？
ロードするファイルは16列までですが、17番目、100番目のフィールドも指定してみましょう。

// 存在しない列番号を指定
select $1, $2, $16, $17, $100 from @CITIBIKE_TRIPS/trips_2013_0_0_0.csv.gz
  (file_format => CSV);

17番目、100番目のフィールドはNULLになりました。
存在しない列番号を指定した場合は、エラーではなくNULL扱いとなるようです。

実験

試しに全てのフィールドを取得する意図で $* を指定したところエラーとなりました。
明示的に列番号を指定する必要があるようです。

select $* from @CITIBIKE_TRIPS/trips_2013_0_0_0.csv.gz (file_format => CSV);

SQLコンパイルエラー： 位置 7 の構文エラー行 1 、予期しない「$」。

クエリ時の関数呼び出し

一部のサポートされた関数は、データファイルをクエリするときに使用することができます。

2番目のフィールドはTIMESTAMP型のようなので、SPLIT_PART関数で年のパートを取得してみましょう。

// SPLIT_PART関数呼び出し
select $1, $2, SPLIT_PART($2, '-', 1) from @CITIBIKE_TRIPS/trips_2013_0_0_0.csv.gz
  (file_format => CSV);

年のパートを取得することができました！

ファイルのメタデータを取得する

Snowflakeは、ステージのファイルに自動的にメタデータを生成します。
このメタデータは、SELECTステートメントを使用してクエリすることが可能です。

次のSQL文を実行して、ステージングされたファイルのメタデータをクエリすることができます。

// メタデータ列を指定
select metadata$filename, metadata$file_row_number, $1, $2 from @CITIBIKE_TRIPS
  (file_format => CSV, pattern=>'citibike-trips/trips_2013_0_.*');

• metadata$filenameは、データファイルの名前です。
• metadata$file_row_numberは、データファイルの各レコードの行番号です。
• $1, $2は、ファイルの列番号を表しており、1番目と2番目のフィールドを指定しています。
• フィールドを正しく解析するために、データ準備時に定義したCSVを指定しています。
• ファイル数が多いのでPATTERNオプションで対象ファイルを絞り込んでいます。

ステージングされたファイルについて、データファイルの名前と列番号を取得することができました。

集計関数の呼び出し

GROUP BYで集計件数を呼び出すことも可能です。
次のSQL文を実行して、ファイルごとの行数、TIMESTAMP型フィールドの最小、最大を取得することができます。

// 集計関数呼び出し
select metadata$filename, count($1), min($2), max($2) from @CITIBIKE_TRIPS
  (file_format => CSV, pattern=>'citibike-trips/trips_2013_0_.*')
  group by 1 order by 1;

メタデータ列をテーブルにロードする

ステージにあるファイルの名前をテーブルにロードしたい場面があるかも知れません。
COPYコマンドと組み合わせることで可能です。

次のSQL文を実行して、テーブルにメタデータ列と通常のデータ列をロードすることができます。

// メタデータ列をテーブルにロード
create or replace table trips_meta (
  filename varchar,
  file_row_number varchar,
  col1 varchar,
  col2 varchar
);

copy into trips_meta
  from(select metadata$filename, metadata$file_row_number, $1, $2 from @CITIBIKE_TRIPS
         (file_format => CSV, pattern=>'citibike-trips/trips_2013_0_.*'));

select * from trips_meta;

テーブルにメタデータ列と通常のデータ列をロードすることができました！

実験

試しに集計関数を使用したクエリをロードしたところ、エラーとなりました。
集計関数などを含まない単純なSELECTのみをサポートしているようです。

create or replace table trips_summary (
  filename varchar,
  cnt number,
  min_dt timestamp,
  max_dt timestamp
);

copy into trips_summary
  from(select metadata$filename, count($1), min($2), max($2) from @CITIBIKE_TRIPS
         (file_format => CSV, pattern=>'citibike-trips/trips_2013_0_.*') group by 1);

SQLコンパイルエラー： COPYステートメントは、インポート用のステージステートメントからの単純なSELECTのみをサポートします。

まとめ

以上、SELECTステートメントを使用してファイルのデータ取得を試してみました。

ステージングされたデータファイルの中身だけでなく、
ファイルの名前や行番号などのメタデータも取得することができました。

データをロードする前またはアンロードした後に、
ステージングされたファイルの内容を検査するのに役立ちそうですね。

参考

• ステージングされたファイルのデータのクエリ — Snowflake Documentation
• ステージングされたファイルのメタデータのクエリ — Snowflake Documentation